﻿IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Expenses_ExpenseType]') AND parent_object_id = OBJECT_ID(N'[dbo].[Expense]'))
ALTER TABLE [dbo].[Expense] DROP CONSTRAINT [FK_Expenses_ExpenseType]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Expenses_Date]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Expense] DROP CONSTRAINT [DF_Expenses_Date]
END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Expenses_Amount]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Expense] DROP CONSTRAINT [DF_Expenses_Amount]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Liquidation_Expense]') AND parent_object_id = OBJECT_ID(N'[dbo].[Liquidation]'))
ALTER TABLE [dbo].[Liquidation] DROP CONSTRAINT [FK_Liquidation_Expense]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Liquidation_PeriodAllocation]') AND parent_object_id = OBJECT_ID(N'[dbo].[Liquidation]'))
ALTER TABLE [dbo].[Liquidation] DROP CONSTRAINT [FK_Liquidation_PeriodAllocation]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PeriodAllocation_ExpenseType]') AND parent_object_id = OBJECT_ID(N'[dbo].[PeriodAllocation]'))
ALTER TABLE [dbo].[PeriodAllocation] DROP CONSTRAINT [FK_PeriodAllocation_ExpenseType]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PeriodAllocation_Period]') AND parent_object_id = OBJECT_ID(N'[dbo].[PeriodAllocation]'))
ALTER TABLE [dbo].[PeriodAllocation] DROP CONSTRAINT [FK_PeriodAllocation_Period]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PeriodAllocation_Amount]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PeriodAllocation] DROP CONSTRAINT [DF_PeriodAllocation_Amount]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PeriodBudget_Period]') AND parent_object_id = OBJECT_ID(N'[dbo].[PeriodBudget]'))
ALTER TABLE [dbo].[PeriodBudget] DROP CONSTRAINT [FK_PeriodBudget_Period]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PeriodBudget_Source]') AND parent_object_id = OBJECT_ID(N'[dbo].[PeriodBudget]'))
ALTER TABLE [dbo].[PeriodBudget] DROP CONSTRAINT [FK_PeriodBudget_Source]
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_PeriodBudget_Amount]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[PeriodBudget] DROP CONSTRAINT [DF_PeriodBudget_Amount]
END

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PositiveExpenses_ExpenseType]') AND parent_object_id = OBJECT_ID(N'[dbo].[PositiveExpenses]'))
ALTER TABLE [dbo].[PositiveExpenses] DROP CONSTRAINT [FK_PositiveExpenses_ExpenseType]
GO

/****** Object:  Table [dbo].[Expense]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Expense]') AND type in (N'U'))
DROP TABLE [dbo].[Expense]
GO

/****** Object:  Table [dbo].[ExpenseType]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExpenseType]') AND type in (N'U'))
DROP TABLE [dbo].[ExpenseType]
GO

/****** Object:  Table [dbo].[Liquidation]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Liquidation]') AND type in (N'U'))
DROP TABLE [dbo].[Liquidation]
GO

/****** Object:  Table [dbo].[Period]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Period]') AND type in (N'U'))
DROP TABLE [dbo].[Period]
GO

/****** Object:  Table [dbo].[PeriodAllocation]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PeriodAllocation]') AND type in (N'U'))
DROP TABLE [dbo].[PeriodAllocation]
GO

/****** Object:  Table [dbo].[PeriodBudget]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PeriodBudget]') AND type in (N'U'))
DROP TABLE [dbo].[PeriodBudget]
GO

/****** Object:  Table [dbo].[PositiveExpenses]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PositiveExpenses]') AND type in (N'U'))
DROP TABLE [dbo].[PositiveExpenses]
GO

/****** Object:  Table [dbo].[Source]    Script Date: 10/24/2012 16:19:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Source]') AND type in (N'U'))
DROP TABLE [dbo].[Source]
GO

/****** Object:  Table [dbo].[Expense]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Expense](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [date] NOT NULL,
	[ExpenseTypeId] [int] NOT NULL,
	[Comment] [varchar](max) NULL,
	[Amount] [money] NOT NULL,
 CONSTRAINT [PK_Expenses] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[ExpenseType]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ExpenseType](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ExpenseType] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[Liquidation]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Liquidation](
	[AllocationId] [int] NOT NULL,
	[ExpenseId] [int] NOT NULL,
 CONSTRAINT [PK_Liquidation] PRIMARY KEY CLUSTERED 
(
	[AllocationId] ASC,
	[ExpenseId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Period]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Period](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Start] [date] NOT NULL,
	[End] [date] NOT NULL,
 CONSTRAINT [PK_Period] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[PeriodAllocation]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PeriodAllocation](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PeriodId] [int] NOT NULL,
	[ExpenseTypeId] [int] NULL,
	[Comment] [varchar](max) NULL,
	[Amount] [money] NULL,
 CONSTRAINT [PK_PeriodAllocation] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[PeriodBudget]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PeriodBudget](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PeriodId] [int] NOT NULL,
	[SourceId] [int] NOT NULL,
	[Comment] [varchar](max) NULL,
	[Amount] [money] NOT NULL,
 CONSTRAINT [PK_PeriodBudget] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[PositiveExpenses]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PositiveExpenses](
	[ExpenseTypeId] [int] NOT NULL
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Source]    Script Date: 10/24/2012 16:19:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Source](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Expense]  WITH CHECK ADD  CONSTRAINT [FK_Expenses_ExpenseType] FOREIGN KEY([ExpenseTypeId])
REFERENCES [dbo].[ExpenseType] ([Id])
GO

ALTER TABLE [dbo].[Expense] CHECK CONSTRAINT [FK_Expenses_ExpenseType]
GO

ALTER TABLE [dbo].[Expense] ADD  CONSTRAINT [DF_Expenses_Date]  DEFAULT (getdate()) FOR [Date]
GO

ALTER TABLE [dbo].[Expense] ADD  CONSTRAINT [DF_Expenses_Amount]  DEFAULT ((0)) FOR [Amount]
GO

ALTER TABLE [dbo].[Liquidation]  WITH CHECK ADD  CONSTRAINT [FK_Liquidation_Expense] FOREIGN KEY([ExpenseId])
REFERENCES [dbo].[Expense] ([Id])
GO

ALTER TABLE [dbo].[Liquidation] CHECK CONSTRAINT [FK_Liquidation_Expense]
GO

ALTER TABLE [dbo].[Liquidation]  WITH CHECK ADD  CONSTRAINT [FK_Liquidation_PeriodAllocation] FOREIGN KEY([AllocationId])
REFERENCES [dbo].[PeriodAllocation] ([Id])
GO

ALTER TABLE [dbo].[Liquidation] CHECK CONSTRAINT [FK_Liquidation_PeriodAllocation]
GO

ALTER TABLE [dbo].[PeriodAllocation]  WITH CHECK ADD  CONSTRAINT [FK_PeriodAllocation_ExpenseType] FOREIGN KEY([ExpenseTypeId])
REFERENCES [dbo].[ExpenseType] ([Id])
GO

ALTER TABLE [dbo].[PeriodAllocation] CHECK CONSTRAINT [FK_PeriodAllocation_ExpenseType]
GO

ALTER TABLE [dbo].[PeriodAllocation]  WITH CHECK ADD  CONSTRAINT [FK_PeriodAllocation_Period] FOREIGN KEY([PeriodId])
REFERENCES [dbo].[Period] ([Id])
GO

ALTER TABLE [dbo].[PeriodAllocation] CHECK CONSTRAINT [FK_PeriodAllocation_Period]
GO

ALTER TABLE [dbo].[PeriodAllocation] ADD  CONSTRAINT [DF_PeriodAllocation_Amount]  DEFAULT ((0)) FOR [Amount]
GO

ALTER TABLE [dbo].[PeriodBudget]  WITH CHECK ADD  CONSTRAINT [FK_PeriodBudget_Period] FOREIGN KEY([PeriodId])
REFERENCES [dbo].[Period] ([Id])
GO

ALTER TABLE [dbo].[PeriodBudget] CHECK CONSTRAINT [FK_PeriodBudget_Period]
GO

ALTER TABLE [dbo].[PeriodBudget]  WITH CHECK ADD  CONSTRAINT [FK_PeriodBudget_Source] FOREIGN KEY([SourceId])
REFERENCES [dbo].[Source] ([Id])
GO

ALTER TABLE [dbo].[PeriodBudget] CHECK CONSTRAINT [FK_PeriodBudget_Source]
GO

ALTER TABLE [dbo].[PeriodBudget] ADD  CONSTRAINT [DF_PeriodBudget_Amount]  DEFAULT ((0)) FOR [Amount]
GO

ALTER TABLE [dbo].[PositiveExpenses]  WITH CHECK ADD  CONSTRAINT [FK_PositiveExpenses_ExpenseType] FOREIGN KEY([ExpenseTypeId])
REFERENCES [dbo].[ExpenseType] ([Id])
GO

ALTER TABLE [dbo].[PositiveExpenses] CHECK CONSTRAINT [FK_PositiveExpenses_ExpenseType]
GO
